package com.dc.dao;

import com.dc.po.IpLog;
import com.dc.vo.IpCount;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author xingzq
 * @version 1.0
 * @date 2022/2/15 11:17
 */
public interface IpLogRepository extends JpaRepository<IpLog,Long> {

    Long countAllByIp(String ip);

    @Query(value = "SELECT i.ip,COUNT(*) AS total,i.ip_msg,i.time FROM (SELECT * FROM blog.ip_log  ORDER BY time DESC LIMIT 100000) i GROUP BY i.ip order by total desc",nativeQuery = true)
    List countIp();

    @Query(value = "SELECT tmp.ip,tmp.total,tmp.ip_msg,i.ip_msg as ip_msg1,tmp.time\n" +
            "FROM (SELECT i.ip,COUNT(*) AS total,i.ip_msg ,i.time FROM (SELECT * FROM blog.ip_log  ORDER BY time DESC LIMIT 100000) i  GROUP BY i.ip \n" +
            ") tmp left outer JOIN (SELECT * FROM blog.ip_log WHERE ip_msg!='未知ip_2' GROUP BY ip) i on tmp.ip = i.ip order by total desc",nativeQuery = true)
    List countIp1();

    List<IpLog> findByIp(String ip);
}
